<?php include 'includes/config.php'; ?>
<?php
//script Advanced search for PeDALS 
/*form to Advanced search thru PeDALS DB 
#########################################################*/

$sql="";
//evaluate Advanced search form-----------------------------------
if(isset($_POST['searchKeword'])){ 
	if(preg_match('/^[A-Za-z0-9 -]*$/',$_POST['searchKeword'])){
		$keword=$_POST['searchKeword'];
	}
	else{
		$provance='<h4 style="color:red;">Special characters not allowed in search box.</h4>';
	}
}
	
if(isset($keword)){//check request form
	//check for Provenance========================================
	if(isset($_POST['ProvenanceMain']) && $_POST['ProvenanceMain']=='on' || (!isset($_POST['ProvenanceMain']) && !isset($_POST['SeriesMain']) && !isset($_POST['ItemsMain']) && !isset($_POST['AcquisitionsMain']))){
		if(isset($_POST['prov'])){$sql="SELECT ProvenanceId, ProvenanceName FROM Provenance WHERE ProvenanceName Like '%".$keword."%'";}
		if(isset($_POST['provName'])){$sql="SELECT Provenance.ProvenanceId, Provenance.ProvenanceName FROM Provenance INNER JOIN ProvenanceVariant 
ON Provenance.ProvenanceId = ProvenanceVariant.ProvenanceId WHERE Provenance.ProvenanceName LIKE '%".$keword."%' OR ProvenanceVariant.ProvenanceVariantName LIKE '%".$keword."%' GROUP BY Provenance.ProvenanceId,Provenance.ProvenanceName";}

		$provance = '<h2 style="border-bottom:1px solid #666666;">Provenance Matches</h2>';
		$provance2="";
		//Provenance display results----------------------------------
			$getData = sqlsrv_prepare( $conn, $sql);
			if($getData===false){echo "Error in preparing statement.<br/><br/><br/><br/>\n";}
			if(sqlsrv_execute($getData)){/*echo "Statement executed. YES\n";*/}else{echo "Error in executing statement.<br/><br/><br/><br/>\n";}
				$counter=0;
				while (sqlsrv_fetch($getData)){
					$provance2.='<a href="provDetail.php?prov='.sqlsrv_get_field($getData,0).'">'.sqlsrv_get_field($getData,1).'</a><br>';
					$counter++;
				}
			$provance.='<p>Your search term <b style="size: 14px; color: blue;">'.$keword.'</b> returned '.$counter.' results from Provenances.</p>';
			$provance.=$provance2;
		}
	//check for Series============================================
	if(isset($_POST['SeriesMain']) && $_POST['SeriesMain']=='on' || (!isset($_POST['ProvenanceMain']) && !isset($_POST['SeriesMain']) && !isset($_POST['ItemsMain']) && !isset($_POST['AcquisitionsMain']))){
		$seriesCheckQuery=0;
		$sql="SELECT SeriesId,SeriesTitle,SeriesDescription,CONVERT(VARCHAR, CreatedDate, 101) AS CreatedDate,CreatedBy FROM Series";
		//check Series title
		if(isset($_POST['provSeriesTitle'])){
			$sql.=" WHERE SeriesTitle Like '%".$keword."%'";
			$seriesCheckQuery=1;
			}
		//check series subject qualifier
		if(isset($_POST['serSubjectQua'])){
			if($seriesCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="SeriesId IN (SELECT SeriesSubject_LINK.SeriesId FROM SeriesSubject_LINK 
								INNER JOIN Qualifier ON SeriesSubject_LINK.QualifierId=Qualifier.QualifierId
								WHERE Qualifier.QualifierType LIKE '%".$keword."%')";
			$seriesCheckQuery=1;
			}
		//check series subject
		if(isset($_POST['serSubject'])){
			if($seriesCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="SeriesId IN (SELECT SeriesSubject_LINK.SeriesId FROM SeriesSubject_LINK 
								INNER JOIN Subject ON SeriesSubject_LINK.SubjectId=Subject.SubjectId 
								WHERE Subject.SubjectName LIKE '%".$keword."%')";
			$seriesCheckQuery=1;
			}
		//check series location
		if(isset($_POST['serLoc'])){
			if($seriesCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="SeriesId IN (SELECT SeriesGeoLocation_LINK.SeriesId FROM SeriesGeoLocation_LINK 
								INNER JOIN GeoLocation ON SeriesGeoLocation_LINK.GeoLocationId=GeoLocation.GeoLocationId
								WHERE GeoLocation.GeoLocationValue LIKE '%".$keword."%')";
			$seriesCheckQuery=1;
			}
		//check series keyword
		if(isset($_POST['serKey'])){
			if($seriesCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="SeriesId IN (SELECT SeriesId FROM SeriesKeyword WHERE SeriesKeyword LIKE '%".$keword."%')";			
			}
		
		$series = '<h2 style="border-bottom:1px solid #666666;">Series Matches</h2>';
		$series2="";
		//Series display results----------------------------------
			$getData = sqlsrv_prepare( $conn, $sql);
			if($getData===false){echo "Error in preparing statement.<br/><br/><br/><br/>\n";}
			if(sqlsrv_execute($getData)){/*echo "Statement executed. YES\n";*/}else{echo "Error in executing statement.<br/><br/><br/><br/>\n";}
				$counter=0;
				while (sqlsrv_fetch($getData)){
					$series2.='
					<a href="SerDetail.php?ser='.sqlsrv_get_field($getData,0).'">'.sqlsrv_get_field($getData,1).'</a> 
					- '.sqlsrv_get_field($getData,2,SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR)).'<br />
					Created: '.sqlsrv_get_field($getData,3).'<br />
					Created by: '.sqlsrv_get_field($getData,4).'
					<br>';
					$counter++;
				}
			$series.='<p>Your search term <b style="size: 14px; color: blue;">'.$keword.'</b> returned '.$counter.' results from Series.</p>';
			$series.=$series2;
		}
	//check for Acquisitions======================================
	if(isset($_POST['AcquisitionsMain']) && $_POST['AcquisitionsMain']=='on' || (!isset($_POST['ProvenanceMain']) && !isset($_POST['SeriesMain']) && !isset($_POST['ItemsMain']) && !isset($_POST['AcquisitionsMain']))){
		$acquisitionCheckQuery=0;
		$sql="SELECT SeriesAcquisitionId,SeriesAcquisitionAccessionNumber,CONVERT(VARCHAR, CreatedDate, 101) AS CreatedDate,CreatedBy FROM SeriesAcquisition";
		//Acquisition number
		if(isset($_POST['acqNumber'])){
			$sql.=" WHERE SeriesAcquisitionAccessionNumber LIKE '%".$keword."%'";
			$acquisitionCheckQuery=1;
		}
		//Acquisition transfer authority
		if(isset($_POST['acqTransfer'])){
			if($acquisitionCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="SeriesAcquisitionTransferAuthority LIKE '%".$keword."%'";
			$acquisitionCheckQuery=1;
		}
		//Acquisition source
		if(isset($_POST['provSource'])){
			if($acquisitionCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="SeriesAcquisitionSource LIKE '%".$keword."%'";	
		}

		$acquisitions = '<h2 style="border-bottom:1px solid #666666;">Acquisitions Matches</h2>';
		$acquisitions2="";
		//Acquisition display results----------------------------------
			$getData = sqlsrv_prepare( $conn, $sql);
			if($getData===false){echo "Error in preparing statement.<br/><br/><br/><br/>\n";}
			if(sqlsrv_execute($getData)){/*echo "Statement executed. YES\n";*/}else{echo "Error in executing statement.<br/><br/><br/><br/>\n";}
				$counter=0;
				while (sqlsrv_fetch($getData)){
					$acquisitions2.='
					<a href="acqDetail.php?acq='.sqlsrv_get_field($getData,0).'">'.sqlsrv_get_field($getData,1).'</a><br />
					Created: '.sqlsrv_get_field($getData,2).'<br />
					Created by: '.sqlsrv_get_field($getData,3).'
					<br>';
					$counter++;
				}
			$acquisitions.='<p>Your search term <b style="size: 14px; color: blue;">'.$keword.'</b> returned '.$counter.' results from Acquisitions.</p>';
			$acquisitions.=$acquisitions2;
		}
	//check for Items=============================================
	if(isset($_POST['ItemsMain']) && $_POST['ItemsMain']=='on' || (!isset($_POST['ProvenanceMain']) && !isset($_POST['SeriesMain']) && !isset($_POST['ItemsMain']) && !isset($_POST['AcquisitionsMain']))){
		$itemCheckQuery=0;
		$sql="SELECT ItemId, ItemTitle, ItemDescription, CONVERT(VARCHAR, CreatedDate, 101) AS CreatedDate, CreatedBy FROM Item";
		//item title
		if(isset($_POST['provItemTitle'])){
			$sql.=" WHERE ItemTitle Like '%".$keword."%'";
			$itemCheckQuery=1;
			}
		//item description
		if(isset($_POST['itmDescription'])){
			if($itemCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="ItemId IN (SELECT Item.ItemId FROM Item
								WHERE Item.ItemDescription LIKE '%".$keword."%')";
			$itemCheckQuery=1;								
			}	
		//item sample
		if(isset($_POST['itmSample'])){
			if($itemCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="ItemId IN (SELECT Item.ItemId FROM Item
								WHERE Item.ItemSample LIKE '%".$keword."%')";
			$itemCheckQuery=1;								
			}				
		//item MIME type
		if(isset($_POST['itmMime'])){
			if($itemCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="ItemId IN (SELECT Item.ItemId FROM Item
								INNER JOIN DigitalFormat ON Item.DigitalFormatId=DigitalFormat.DigitalFormatId
								WHERE DigitalFormat.DigitalFormatMIMEType LIKE '%".$keword."%')";
			$itemCheckQuery=1;
			}
		//item format
		if(isset($_POST['itmFormat'])){
			if($itemCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="ItemId IN (SELECT Item.ItemId FROM Item
								INNER JOIN DigitalFormat ON Item.DigitalFormatId=DigitalFormat.DigitalFormatId
								WHERE DigitalFormat.DigitalFormatFileType LIKE '%".$keword."%')";	
			$itemCheckQuery=1;											
			}
		//item locations
		if(isset($_POST['itmLocations'])){
			if($itemCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="ItemId IN (SELECT Item.ItemId FROM Item 
								INNER JOIN ItemGeoLocation_LINK ON Item.ItemId = ItemGeoLocation_LINK.ItemId 
								CROSS JOIN GeoLocation	
								WHERE GeoLocation.GeoLocationValue LIKE '%".$keword."%')";
			$itemCheckQuery=1;											
			}	
		//item subjects
		if(isset($_POST['itmSubjects'])){
			if($itemCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="ItemId IN (SELECT Item.ItemId FROM Item 
								INNER JOIN ItemSubject_LINK ON Item.ItemId = ItemSubject_LINK.ItemId 
								CROSS JOIN Subject
								WHERE Subject.SubjectName LIKE '%".$keword."%')";
			$itemCheckQuery=1;											
			}					
		//item keywords
		if(isset($_POST['itmKeywords'])){
			if($itemCheckQuery==1){$sql.=" OR ";}else{$sql.=" WHERE ";}
			$sql.="ItemId IN (SELECT Item.ItemId FROM Item
								INNER JOIN ItemKeyword ON Item.ItemId = ItemKeyword.ItemId
								WHERE ItemKeyword.Keyword LIKE '%".$keword."%')";				
			}
			
			
		$items = '<h2 style="border-bottom:1px solid #666666;">Item Matches</h2>';
		$items2="";
		//Items display results----------------------------------
			$getData = sqlsrv_prepare( $conn, $sql);
			if($getData===false){echo "Error in preparing statement.<br/><br/><br/><br/>\n";}
			if(sqlsrv_execute($getData)){/*echo "Statement executed. YES\n";*/}else{echo "Error in executing statement.<br/><br/><br/><br/>\n";}
				$counter=0;
				while (sqlsrv_fetch($getData)){
					$items2.='
					<a href="itemDetail.php?id='.sqlsrv_get_field($getData,0).'">'.sqlsrv_get_field($getData,1).'</a>
					- '.sqlsrv_get_field($getData,2,SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR)).'<br />
					Created: '.sqlsrv_get_field($getData,3).'<br />
					Created by: '.sqlsrv_get_field($getData,4).'
					<br>';
					$counter++;
				}
			$items.='<p>Your search term <b style="size: 14px; color: blue;">'.$keword.'</b> returned '.$counter.' results from Items.</p>';
			$items.=$items2;
		}		
}//check request form

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta name="description" content="Persistent Digital Archives and Library System" />
<title>PeDALS Web Administration</title>
<link rel="shortcut icon" href="images/favicon.ico" />
<link href="includes/style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<style type="text/css">
.showOptions{background-color:#dcf6db; cursor:pointer; padding-bottom:3px;}
.showOptions:hover{background-color:#cee2cd;}
.options{border:1px solid #008000; padding:4px;}
.labelTitle{padding:0px; color:#333; font-weight:bold; font-size:12px;}
</style>
<script type="text/javascript">
function showOption(id){
	if(document.getElementById(id).style.display=='none'){
		document.getElementById(id).style.display='';
		}
	else{
		document.getElementById(id).style.display='none';
		}
	}
</script>
<?php include 'includes/header.php'; ?>
<!---SERACH--->
<div id="breadcrumb-container">
  <div id="breadcrumb"><a href="index.php" title="Home page">Home</a> / Advanced Search</div>
  <div id="breadcrumb-side"><form name="search" action="search.php"><input type="text" name="q" width="50"/><input type="submit" value="Go" /></form><a href="advSearch.php" title="Advanced Search" alt="Advanced Search">Advanced Search</a>
  </div>
  <div class="clear"></div>
</div>
<!--CONTENT-->
<div id="content-container">
<!--SIDE COLUMN-->
  <div id="content-side">
    <ul class="link-list-vertical">
      <li><a href="provSummary.php" title="Manage provenance data">Provenances</a></li>
      <li><a href="serSummary.php" title="Manage series data">Series</a></li>
      <li><a href="acqSummary.php" title="Manage acquisition data">Acquisitions</a></li>
    </ul>
  </div>
<!--MAIN COLUMN-->
  <div id="content"><a name="maincontent" id="maincontent"></a>
    <h1>Advanced Search PeDALS</h1>   
  	  <!---form to adv search--->
      <form method="post">
      <table width="100%">
      <tr><td align="left"><input type="text" size="60" name="searchKeword"/>&nbsp;<input type="submit" value="Search"/></td></tr>
      <tr><td class="showOptions">
      	<label class="labelTitle"><input type="checkbox" name="ProvenanceMain" onchange="showOption('Provenance');"/>Provenance</label>
      </td></tr>
          <!---Provenance options--->
            <tr id="Provenance" style="display:none;"><td>
            <fieldset class="options"><legend>Provenance items</legend>
                    <label><input type="checkbox" name="prov" checked="checked"/>Provenance</label>
                    <label><input type="checkbox" name="provName" checked="checked"/>Provenance variant name</label>                    
            </fieldset>
            </td></tr>
          <!---End Provenance options--->
      <tr><td class="showOptions">
      	<label class="labelTitle"><input type="checkbox" name="SeriesMain" onchange="showOption('Series');"/>Series</label>
      </td></tr>
          <!---Series options--->
            <tr id="Series" style="display:none;"><td>
            <fieldset class="options"><legend>Series items</legend>
            		<label><input type="checkbox" name="provSeriesTitle" checked="checked"/>Series Title</label>
                    <label><input type="checkbox" name="serSubjectQua" checked="checked"/>Series subject qualifier</label>
                    <label><input type="checkbox" name="serSubject" checked="checked"/>Series subject</label>
                    <label><input type="checkbox" name="serLoc" checked="checked"/>Series location</label>
                    <label><input type="checkbox" name="serKey" checked="checked"/>Series keywords</label>
            </fieldset>
            </td></tr>
          <!---End Series options--->      
      <tr><td class="showOptions">
      	<label class="labelTitle"><input type="checkbox" name="AcquisitionsMain" onchange="showOption('Acquisitions');"/>Acquisitions</label>
      </td></tr>
          <!---Acquisitions options--->
            <tr id="Acquisitions" style="display:none;"><td>
            <fieldset class="options"><legend>Acquisitions items</legend>
                    <label><input type="checkbox" name="acqNumber" checked="checked"/>Acquisition number</label>
                    <label><input type="checkbox" name="acqTransfer" checked="checked"/>Acquisition transfer authority</label>
                    <label><input type="checkbox" name="provSource" checked="checked"/>Source</label>
            </fieldset>
            </td></tr>
          <!---End Acquisitions options--->      
      <tr><td class="showOptions">
      	<label class="labelTitle"><input type="checkbox" name="ItemsMain" onchange="showOption('Items');"/>Items</label>
      </td></tr>
          <!---Items options--->
            <tr id="Items" style="display:none;"><td>
            <fieldset class="options"><legend>Items</legend>
                    <label><input type="checkbox" name="provItemTitle" checked="checked"/>Item Title</label>
                    <label><input type="checkbox" name="itmDescription" checked="checked"/>Item Description</label>
                    <label><input type="checkbox" name="itmSample" checked="checked"/>Item Sample Text</label>                    
                    <label><input type="checkbox" name="itmKeywords" checked="checked"/>Item Keyword</label>
                    <label><input type="checkbox" name="itmLocations" checked="checked"/>Item Location</label>   
                    <label><input type="checkbox" name="itmSubjects" checked="checked"/>Item Subject</label>                                        
                    <label><input type="checkbox" name="itmMime" checked="checked"/>Item MIME type</label>
                    <label><input type="checkbox" name="itmFormat" checked="checked"/>Item format</label>
            </fieldset>
            </td></tr>
      	  <!---End Items options--->
      <tr><td align="right"><input type="submit" value="Search"/></td></tr>
      </table>
      
      </form>
      <!---end form to adv search--->
    <div class="clear"></div>
    <?php
    if(isset($provance)){echo $provance;}	
	if(isset($series)){echo $series;}
	if(isset($acquisitions)){echo $acquisitions;}
	if(isset($items)){echo $items;}
	?>
<!--FOOTER-->
	<?php include 'includes\footer.php'; ?>
  </div>
</div>
</body>
</html>